﻿// -----------------------------------------------------------------------
// <copyright file="CalendarData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db Calendar
    /// </summary>
    public class CalendarData
    {
        /// <summary>
        /// Get Calendar By Id
        /// </summary>
        /// <param name="calendarId">Param calendarId</param>
        /// <returns>Return Calendar</returns>
        public CalendarEN GetCalendarById(int calendarId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                CalendarEN calendar = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCalendarGetById, calendarId);
                while (reader.Read())
                {
                    calendar = new CalendarEN();

                    if (!reader.IsDBNull(0))
                    {
                        calendar.CalendarId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        calendar.Year = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        calendar.Date = reader.GetDateTime(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        calendar.IsHoliday = reader.GetBoolean(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        calendar.Descriptions = reader.GetString(4);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return calendar;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all Calendar
        /// </summary>
        /// <returns>Return list all Calendar</returns>
        public IList<CalendarEN> GetCalendars()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CalendarEN> lstCalendar = new List<CalendarEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCalendarGetAll);
                while (reader.Read())
                {
                    CalendarEN calendar = new CalendarEN();

                    if (!reader.IsDBNull(0))
                    {
                        calendar.CalendarId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        calendar.Year = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                       calendar.Date = reader.GetDateTime(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        calendar.IsHoliday = reader.GetBoolean(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        calendar.Descriptions = reader.GetString(4);
                    }

                    lstCalendar.Add(calendar);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstCalendar;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db Calendar
        /// </summary>
        /// <param name="calendar">Paarm Calendar</param>
        /// <returns>Insert Calendar</returns>
        public int Insert(CalendarEN calendar)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Calendar.Year, calendar.Year),
                                    new SqlParameter(Constants.SqlParamerterName.Calendar.Date, (object)calendar.Date ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Calendar.IsHoliday, calendar.IsHoliday),
                                    new SqlParameter(Constants.SqlParamerterName.Calendar.Descriptions, calendar.Descriptions)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCalendarInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db Calendar
        /// </summary>
        /// <param name="calendar">Param Calendar</param>
        public void Update(CalendarEN calendar)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Calendar.CalendarId, calendar.CalendarId),
                                    new SqlParameter(Constants.SqlParamerterName.Calendar.Year, calendar.Year),
                                    new SqlParameter(Constants.SqlParamerterName.Calendar.Date, (object)calendar.Date ?? DBNull.Value),
                                    new SqlParameter(Constants.SqlParamerterName.Calendar.IsHoliday, calendar.IsHoliday),
                                    new SqlParameter(Constants.SqlParamerterName.Calendar.Descriptions, calendar.Descriptions)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCalendarUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete Calendar by calendarId
        /// </summary>
        /// <param name="calendarId">param calendarId</param>
        public void Delete(int calendarId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCalendarDelete, calendarId);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Calendar is insert by year?
        /// </summary>
        /// <param name="year">param year</param>
        /// <returns>Return true or false</returns>
        public bool IsInsertedByYear(int year)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VONECalendarIsInsertedByYear, year));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert Calendar By Year
        /// </summary>
        /// <param name="year">Param year</param>
        public void InsertByYear(int year)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VONECalendarInsertByYear, year);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete Calendar By Year
        /// </summary>
        /// <param name="year">Param year</param>
        public void DeleteByYear(int year)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VONECalendarDeleteByYear, year);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get Calendar By MonthYear
        /// </summary>
        /// <param name="month">Param month</param>
        /// <param name="year">Param yearr</param>
        /// <returns>Return list Calendar By Month, Year</returns>
        public IList<CalendarEN> GetCalendarByMonthYear(int month, int year)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<CalendarEN> lstCalendar = new List<CalendarEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VONECalendarByMonthYear, month, year);
                while (reader.Read())
                {
                    CalendarEN calendar = new CalendarEN();

                    if (!reader.IsDBNull(0))
                    {
                        calendar.CalendarId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        calendar.Year = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        calendar.Date = reader.GetDateTime(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        calendar.IsHoliday = reader.GetBoolean(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        calendar.Descriptions = reader.GetString(4);
                    }

                    lstCalendar.Add(calendar);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstCalendar;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get Calendar by Date
        /// </summary>
        /// <param name="date">Param date</param>
        /// <returns>Return CalendarEN</returns>
        public CalendarEN GetCalendarByDate(DateTime date)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                CalendarEN calendar = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneCalendarGetByDate, date);
                while (reader.Read())
                {
                    calendar = new CalendarEN();

                    if (!reader.IsDBNull(0))
                    {
                        calendar.CalendarId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        calendar.Year = reader.GetInt32(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        calendar.Date = reader.GetDateTime(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        calendar.IsHoliday = reader.GetBoolean(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        calendar.Descriptions = reader.GetString(4);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return calendar;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}